/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DataAccess;

import model.BillDetailsReceipt;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLDataException;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JComboBox;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;

/**
 *
 * @author Home
 */
public class TableAccess {

    public static boolean readToTable(String sql, JTable tableName) {
        try {
            int nCount = 0;
            DefaultTableModel dm = (DefaultTableModel) tableName.getModel();
            for (int i = dm.getRowCount() - 1; i >= 0; i--) {
                dm.removeRow(i);
            }
            Vector cols = new Vector();
            Statement st = DBUtil.getConnection(DBUtil.getInfoDB()).createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = st.executeQuery(sql);
            ResultSetMetaData rm = rs.getMetaData();
            for (int i = 1; i <= rm.getColumnCount(); i++) {
                cols.add(rm.getColumnName(i));
            }
            dm.setColumnIdentifiers(cols);
            while (rs.next()) {
                Vector rows = new Vector();
                for (int i = 1; i <= rm.getColumnCount(); i++) {
                    rows.add(rs.getString(i));
                    nCount++;
                }
                dm.addRow(rows);
            }
            tableName.setModel(dm);
            if (nCount > 0) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            Logger.getLogger(TableAccess.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }

    }


     public static boolean readToTableCus(String sql, JTable tableName) {
        try {
            int nCount = 0;
            DefaultTableModel dm = (DefaultTableModel) tableName.getModel();
            for (int i = dm.getRowCount() - 1; i >= 0; i--) {
                dm.removeRow(i);
            }
            Vector cols = new Vector();
            Statement st = DBUtil.getConnection(DBUtil.getInfoDB()).createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = st.executeQuery(sql);
            ResultSetMetaData rm = rs.getMetaData();
            for (int i = 1; i <= rm.getColumnCount(); i++) {
                cols.add(rm.getColumnName(i));
            }
            dm.setColumnIdentifiers(cols);
            while (rs.next()) {
                Vector rows = new Vector();
//                for (int i = 1; i <= rm.getColumnCount(); i++) {
//                    rows.add(rs.getString(i));
//                    nCount++;
//                }
                nCount++;
                rows.addElement(rs.getInt(1));
                rows.addElement(rs.getString(2));
                rows.addElement(rs.getString(3));
                rows.addElement(rs.getString(4));
                rows.addElement(rs.getString(5));
//                rows.addElement(rs.getBoolean(6));
                if (rs.getBoolean(6) == true) {
                    rows.addElement("Male");
                } else {
                    rows.addElement("Female");
                }
                dm.addRow(rows);
            }
            tableName.setModel(dm);
            if (nCount > 0) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            Logger.getLogger(TableAccess.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }

    }

    public static boolean readToTableBill(String sql, JTable tableName) {
        try {
            int nCount = 0;
            DefaultTableModel dm = (DefaultTableModel) tableName.getModel();
            for (int i = dm.getRowCount() - 1; i >= 0; i--) {
                dm.removeRow(i);
            }
            Vector cols = new Vector();
            Statement st = DBUtil.getConnection(DBUtil.getInfoDB()).createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = st.executeQuery(sql);
            ResultSetMetaData rm = rs.getMetaData();
            for (int i = 1; i <= rm.getColumnCount(); i++) {
                cols.add(rm.getColumnName(i));
            }
            dm.setColumnIdentifiers(cols);
            while (rs.next()) {
                Vector rows = new Vector();
//                for (int i = 1; i <= rm.getColumnCount(); i++) {
//                    rows.add(rs.getString(i));
//                    nCount++;
//                }
                nCount++;
                rows.addElement(rs.getInt(1));
                rows.addElement(rs.getInt(2));
                rows.addElement(rs.getInt(3));
                rows.addElement(rs.getDate(4));
                rows.addElement(rs.getFloat(5));
                rows.addElement(rs.getString(6));
                rows.addElement(rs.getBoolean(7));

                dm.addRow(rows);
            }
            tableName.setModel(dm);
            if (nCount > 0) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            Logger.getLogger(TableAccess.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }

    }

    public static void readToCombo(String sql, JComboBox cboType, String fieldName) {
        try {
            Statement st = DBUtil.getConnection(DBUtil.getInfoDB()).createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next()) {
                String str = rs.getString(fieldName);
                cboType.addItem(str);
            }
        } catch (SQLException ex) {
            Logger.getLogger(TableAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static boolean  readToReceiptTable(String sql, JTable tableName) {
        model.BillDetailsReceipt dm;
        try {
            Statement st = DBUtil.getConnection(DBUtil.getInfoDB()).createStatement();
            ResultSet rs = st.executeQuery(sql);
            ArrayList<Entity.BillSellDetails> vec = new ArrayList<Entity.BillSellDetails>();
            Entity.BillSellDetails billdetails;
            while (rs.next()) {
                billdetails = new Entity.BillSellDetails();
                billdetails.setBillID(rs.getInt(1));
                billdetails.setItemID(rs.getInt(2));
                billdetails.setQuantity(rs.getInt(3));
                billdetails.setQuantityReceipt(rs.getInt(4));
                vec.add(billdetails);
            }
            dm = new BillDetailsReceipt(vec);
            tableName.setModel(dm);
            if (vec.size() == 0) {
                return false;
            } else {
                return true;
            }
        } catch (Exception e) {
        }
        return true;
    }
}
